package com.quanquan.store.repository;

import com.quanquan.store.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @ClassName:OrderRepository
 * @Description:TODO
 * @Author:xiongjunbao
 * @Date:2019/3/21 16:00
 * @Version 1.0
 */
public interface OrderRepository extends JpaRepository<Order, String>{

    @Query(value = "SELECT * FROM oo_order WHERE user_id = ?1 AND status_show = 0 and status = 1 order by create_time desc limit ?2,?3 ", nativeQuery = true)
    List<Order> queryByUserId(String userId, Integer count, Integer szie);

    @Query(value = "SELECT * FROM oo_order WHERE user_id = ?1 and LOCATE(?2,create_time)>0 AND status_show = 0 and status = 1", nativeQuery = true)
    List<Order> queryByUserIdPerDay(String userId, String today);

    @Query(value = "select * from oo_order where order_num = ?1",nativeQuery = true)
    Order findByOrderNum(String orderNum);


    /**
     * 查询当年用户积分商城消费总积分
     *
     * @param userId
     * @return
     */
    @Query(value = "SELECT SUM(integral) FROM oo_order WHERE YEAR(create_time)=YEAR(NOW()) AND user_id = ?1 and status = 1", nativeQuery = true)
    Integer queryIntegralByUserIdYear(String userId);

    /**
     * 查询当年用户积分商城消费总积分
     *
     * @param userId
     * @return
     */
    @Query(value = "SELECT SUM(price) FROM oo_order WHERE YEAR(create_time)=YEAR(NOW()) AND user_id = ?1 and status = 1", nativeQuery = true)
    Double queryPriceByUserIdYear(String userId);


    /**
     * 根据券的购买数量进行排序
     * @return
     */
    @Query(value = "SELECT c.name,a.count,a.integral,a.price,a.coupon_id FROM (SELECT COUNT(1) AS COUNT,coupon_id,price,integral FROM oo_order WHERE STATUS = 1 GROUP BY coupon_id ORDER BY COUNT DESC) a INNER JOIN oo_coupon c WHERE c.id = a.coupon_id ORDER BY a.count DESC,c.create_time ASC limit ?1,?2", nativeQuery = true)
    List<Object[]> queryByCount(Integer count, Integer size);

    /**
     * 根据券的购买数量进行排序
     * @return
     */
    @Query(value = "SELECT c.name,a.count,a.integral,a.price,a.coupon_id FROM (SELECT COUNT(1) AS COUNT,coupon_id,price,integral FROM oo_order WHERE STATUS = 1 GROUP BY coupon_id ORDER BY COUNT DESC) a INNER JOIN oo_coupon c WHERE c.id = a.coupon_id ORDER BY a.count DESC,c.create_time ASC", nativeQuery = true)
    List<Object[]> queryBySumCount();

    /**
     * 某个券对应的订单记录
     * @return
     */
    @Query(value = "SELECT a.create_time,a.order_num,u.name AS user_name FROM (SELECT * FROM oo_order WHERE coupon_id = ?1 AND STATUS = 1) a INNER JOIN oo_user u WHERE u.id = a.user_id", nativeQuery = true)
    List<Object[]> queryByCouponId(String couponId);

    @Query(value = "SELECT * FROM oo_order WHERE user_id = ?1 and coupon_id = ?2 and status = 0 order by create_time desc limit 1", nativeQuery = true)
    Order queryByUserIdAndCouponId(String userId, String couponId);


}
